Sales Opportunities from 2024 Quarter 2
Centers for Medicaid and Medicare Services Data

Prepared By: Chris Mims

15 November 2024

With Clipboard Health's mission of revolutionizing the marketplace for healthcare talent by paving the way in reliability, affordability, and ease of use for both facilities and and healthcare professionals, Clipboard Health could make a strong impact for long-term care facilities and their residents. The Centers for Medicaid and Medicare Services (CMS) compiles and publishes a public data set that details the number of payroll hours worked by healthcare professionals on a daily basis at each of the participating facilities. Insights from the data contained in the Q2 2024 CMS Payroll Based Journal Nurse Staffing report lead to the following recommendations.

High Level Overview of Available Data

Of the 14564 facilities contained in the Q2 2024 CMS data, there are 5750 facilities that are not currently utilizing independent healthcare professionals (contractors).

Looking at the large number of facilities that could be targeted, a more detailed analysis of the data could narrow the focus to a subset of facilities. This would reduce the amount of resources needed and allow for Clipboard Health's philosophy of being dedicated to deeply understanding each community, striving to make life easier for facilities, healthcare professionals, and patients alike. Breaking the facilities withoout contractor hours into regions as defined by the CDC, which can be found here, each region's potential based on the number of facilities in each state can be observed. Now, knowing which geographic areas could be most beneficial and which states within those regions are most saturated, a closer look at other metics might be helpful.

One metric that stood out while diving into the data is the distribution of hours over the type of healthcare professional. Since the data spans over the last completed quarter (Q2 2024) and over verying sizes of facilities, the data is best ingested by averaging the number of hours per resident per day. From the boxplots below, the majority of hours reported come from Certified Nursing Assistants (CNAs); with a median of just over 2. CNAs, Licensed Practical Nurses (LPNs), and Registered Nurses (RNs), are the top 3 most utilized positons.

These insights give us high-level locations by region and state, the number of facilities in those locations, and the overall usage of each profession within facilities. Another dimension to this data is the ownership type of the facilities. This data comes from the Provider Information dataset. There are 3 top level ownership types: for profit, non profit, and government; each with their own sub types. Taking a look at the break out for all facilities and facilities that do not utilize contractors, there does not seem to be a significant difference between the proportions of ownership types.

Now that the high level analysis of the data has been completed, there still seems to be a key metric missing that could guide the sales team to locations that would benefit most from Clipboard Health's offerings. In the Provider Information dataset, there are many metrics for each facility that rates their performance. Taking a look into some of these rating may give insights as to which facilities could benefit from the use of supplemental contractor hours.

Target facilities that:

• Are currently not utilizing independent healthcare professionals (contractors).
• Have an average nursing staff hours per resident per day that is below the state average.
• Also have an overall rating that is below that of the state average.

After looking into the relationships between the number of reported hours and some of the rating metrics that are in the Provider Information dataset, there were no highly correlated pairings. The best pairings did show some positive, though weak, correlations between the average number of registered nurse or certified nurse assistant hours per resident per day and the overall and staffing ratings. Since these showed the highest corrleation, a common sense insight would be to target those facilities that have low average staffing hours per resident per day and low overall ratings. With regulations varying from state to state, comparing facilities to others within the same state allows for better comparison. By taking the average of the number of staff hours per resident per day and overall ratings for each state and then calculating the difference between each facility's metrics and their state averages, facilities whose differences are the most negative would benefit the most from Clipboard Health's services.

Below are two maps showing the locations of facilites who's average staff hours per resident per day and overall rating scores fall below that of their state's average for that metric, with the map on the left filtered by average staff hours per resident per day and the map on the right filtered by overall score. In order to focus on facilities that would benefit the most from the addition of staff hours by contractors, only facilites that are below average in both metrics are shown. Each map can be filtered by the top level ownership type, as well as, clicking on each of the legend options will hide or show that option within the map. The size of the bubble is relative to the difference between that facility's metric and the state average. The further away from the average, the larger the size of the bubble. Hovering over each of the bubbles will show a pop-up box with details about the facility, including their name, address, and a few metrics.

Facilities with Available Beds

The ability to increase staff through Clipboard Health's platform, enables facilites to fill available beds. By taking the number of available beds, mutiplying that by the state average number of hours per resident per day, and dividing by a typical 8 hour shift, the number of additional staff is computed. The sales team could focus on facilities that would need to increase staff by any given number of additional staff required to meet the state's average. Focusing on facilities that would require many additional staff, or those that would need shorter or infrequent additional staff would not benefit from filling available beds. Also, only those facilities that have an above average overall rating and who's average staff hours per resident per day is above their state average should be targeted. Since any facility not at or above these averages, would not likely benefit from opening more beds, but rather by supplementing their staffing needs to improve in these metrics.

Assumptions made:

• Same amount of staff is needed for each day of the week
• One shift = 8 hours
• Only CNA and RN shifts will be considered

Another calculation that could be done, if sales deems it to be necessary, is to find the correct mix of LPN, RN, and CNA shifts to better support filling any available beds.

Facilities with Average Care Hours per Resident per Day Below CMS Requirements

The Centers for Medicare & Medicaid Services recently changed their guidelines on the number of hours of direct care per resident per day (HPRD) in a long-care facility that recieves Medicare or Medicaid funds. These new guidelines mandate that each resident recieve a minimum of 3.48 HPRD with 0.55 hours coming from a registered nurse, 2.45 hours coming from a certified nursing aide, and the remaining hours coming from a combination of any nursing staff, including LPNs. Another part of the mandate is that a registered nurse must be on-site 24 hours a day, 7 days a week. CMS released this final rule April 22nd, 2024, with a phased implementation. Non-rural area facilities have two years to implement the 24/7 on-site RN and 3.48 total HPRD rules, and three years to implement the 0.55 RN/2.45 CNA HPRD rules. Rural area facilities must implement these changes within three years and five years respectively. ("CMS releases final rule," 2024).

With these new rules in mind, the data contained in the Q2 2024 CMS Payroll Based Journal Nurse Staffing report can highlight those facilities that, currently, would be deficient. Clipboard Health can then use this information to reach out to those facilities and offer their services to ensure that the facility has the ability to be in compliance with the new rules. Since there are four separate rules that factor into whether a facility is in compliance or not, much care needs to be taken while determining if the facility is, in fact, deficient. Direct interpretation of the new rule makes a facility deficient if any of the four rules are not met.

When determining if the facility meets the 3.48 HPRD rule, the average HPRD of the total care hours must be at or above 3.48, the average RN hours must be above 0.55, and the average CNA hours must be above 2.45. Furthermore, the facility must have at least 24 hours per day of RN and/or RN Director of Nursing hours to meet the 24/7 rule. There are exemptions that can be applied to these rules, but the data needed to determine if a facility is exempt from some or all of these rules is not found within the datasets on the CMS website. To the right is a map of all facilities that, if the new rules were in effect today, would be deficient. They are color coded based on the number of deficiencies.

Conclusions

Even though there were no clear correlations to be foudn within in the data, some commonsense deductions could be used to focus in on facilities that could benefit from Clipboard Health's services. Facilities that are below the state averages for both staff hours per resident per day and their overall rating may be in need of additional hours to help increase these metrics. In other facilities that are already exceeding the state averages in these two areas and have available beds, the addition of new staff may be needed in order to fill those open beds. And with Clipboard Health's policy of not charging a facility for hiring a contracted employee, both the facility and the contractor could have a trial period without all the stress of taking on a new employee/employer directly. Lastly, with CMS instituting new rules that will be phased in over the next two to five years, filling gaps in staffing with contracted employees from Clipboard Health's marketplace could bring a facility into compliance quickly and easily. The suggestions contained in this document are still high level suggestions and could be refined with a larger deep-dive into specific areas and their demographics.

Citations

CMS releases final rule requiring minimum staffing standards for nursing homes. (2024, April 30). Retrieved from https://www.wsha.org/articles/cms-releases-final-rule-requiring-minimum-staffing-requirements-for-nursing-homes/

SQL Test Answers

-- QUESTION #1
SELECT
    c.customer_name
    ,p.product_name
    ,s.total_amount
FROM sales AS s 
LEFT JOIN customers AS c 
    ON s.customer_id = c.customer_id 
LEFT JOIN products AS p 
    ON s.product_id = p.product_id 
WHERE s.sale_date >= ADDDATE(CURDATE(), INTERVAL -30 DAY);

--QUESTION #2
SELECT 
    p.category
    ,SUM(s.total_amount)
FROM sales AS s 
LEFT JOIN products AS p 
    ON s.product_id = p.product_id
WHERE s.sale_date >= ADDDATE(CURDATE(), INTERVAL -1 YEAR)
GROUP BY p.category;

-- QUESTION #3
SELECT 
    DISTINCT c.customer_name
FROM sales AS s 
LEFT JOIN customers AS c
    ON s.customer_id = c.customer_id
WHERE YEAR(s.sale_date) = 2023
    AND c.sales_region = 'West';

-- QUESTION #4
SELECT
    c.customer_name as "Customer Name"
    ,COUNT(DISTINCT s.sales_id) as "Total Number of Sales"
    ,SUM(s.quantity) as "Total Quantity Sold"
    ,SUM(s.quantity * p.price) as "Total Revenue"
FROM sales AS s 
LEFT JOIN customers AS c 
    ON s.customer_id = c.customer_id
LEFT JOIN products AS p 
    ON s.product_id = p.product_id
GROUP BY c.customer_id;

-- QUESTION 5
SELECT 
    c.customer_name
    ,SUM(s.quantity * p.price) as "Total Revenue"
FROM sales AS s 
LEFT JOIN customers AS c
    ON s.customer_id = c.customer_id
LEFT JOIN products AS p 
    ON s.product_id = p.product_id
WHERE YEAR(s.sale_date) = 2023
GROUP BY c.customer_id
ORDER BY "Total Revenue" DESC
LIMIT 3;

-- QUESTION 6
WITH ttl_qnty AS (
    SELECT 
        p.product_id
        ,SUM(s.quantity) as tqs
    FROM sales AS s 
    LEFT JOIN products AS p 
        ON s.product_id = p.product_id
    WHERE YEAR(s.sale_date) = 2023
    GROUP BY s.product_id 
)
SELECT 
    p.product_name 
    ,t.tqs as "Total Quantity Sold"
    ,RANK() OVER (ORDER BY t.tqs DESC) as "Rank"
FROM ttl_qnty AS t
LEFT JOIN products AS p
    ON p.product_id = t.product_id
ORDER BY t.tqs DESC;

-- QUESTION #7
SELECT 
    customer_name
    ,sales_region 
    ,CASE 
        WHEN sign_up_date >= ADDDATE(CURDATE(), INTERVAL -6 MONTH) THEN "New"
        ELSE "Existing"
    END as category 
FROM customers;

-- QUESTION #8
WITH ttl_sales AS (
    SELECT 
        LAST_DAY(s.sale_date) as ldotm
        ,p.price * s.quantity as ttl_revenue
    FROM sales AS s 
    LEFT JOIN products AS p 
        ON s.product_id = p.product_id
    WHERE s.sale_date >= ADDDATE(CURDATE(), INTERVAL -12 MONTH)
)
SELECT 
    CONCAT(MONTHNAME(ldotm), ' ', YEAR(ldotm)) as month_year
    ,SUM(ttl_revenue) as total_sales
FROM ttl_sales
GROUP BY ldotm;

-- QUESTION #9
SELECT
    p.category
FROM sales AS s 
LEFT JOIN products AS p
    ON s.product_id = p.product_id
WHERE s.sale_date >= ADDDATE(CURDATE(), INTERVAL -6 MONTH)
GROUP BY p.category
HAVING SUM(p.price * s.quantity) > 50000;

-- QUESTION #10
SELECT 
    s.sales_id
FROM sales AS s 
LEFT JOIN products AS p 
    ON s.product_id = p.product_id
WHERE (p.price * s.quantity) != s.total_amount;

Clipboard Health Case Study by Christopher Mims is licensed under CC BY-NC 4.0